*****************************************************************
*****************************************************************
*** 0. General settings
*****************************************************************
*****************************************************************

* clear
clear
clear 		mata
mat 		drop _all

* set path
global		path	""				


use "$path/0 Data/_Processed/Master_beforecompu_exposures", clear
rename gvkey gvkey_lender
	merge n:1 borrowercompanyid using "$path/0 Data/_Processed/Roberts-link"
	drop if _merge == 2
	drop _merge
	
save "$path/0 Data/_Processed/Master_beforecompu_exposures_with_gvkeys", replace


save "$path/0 Data/_Processed/industry_level_spreads", replace
*/

use  "$path/0 Data/CapitalIQ/credit_lines_with_sic_codes.dta", clear

keep if inrange(quarter,tq(2019q4),tq(2020q2))
drop if gvkey == .

sort gvkey quarter

gen exposure_2019q4 = total_balance if inrange(quarter,tq(2019q4),tq(2019q4))
bysort gvkey: replace exposure_2019q4 = exposure_2019q4[_n-1] if missing(exposure_2019q4)

keep if inrange(quarter,tq(2020q1),tq(2020q2))
by gvkey: gen usage_change_q2 = -(usage-usage[_n-1])
by gvkey: replace usage_change_q2 = usage_change_q2[_n+1] if usage_change_q2 == .

keep if inrange(quarter,tq(2020q1),tq(2020q1))
*gen repayment_firm_q2 = usage_change_q2*(undrawn_rc+rc)
gen repayment_firm_q2 = usage_change_q2*total_balance

*** merge with ratings
merge 1:1 gvkey using "$path/0 Data/_Processed/borrower_ratings"
drop if _merge == 2
drop _merge

*** merge with bond spreads
* put quarter to 2020Q2 to obtain the average bond spread from this period
replace quarter = quarter + 1
merge m:1 quarter using "$path/0 Data/_Processed/bonds_spreads_quarterly_averages"
drop if _merge == 2
drop _merge


* define adequate bond spread for each firm
gen bond_spread = .
replace bond_spread = tripleA if splticrm == "AAA"
replace bond_spread = doubleA if (splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-")
replace bond_spread = singleA if (splticrm == "A+" | splticrm == "A" | splticrm == "A-")
replace bond_spread = tripleB if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
replace bond_spread = nonIG if splticrm != "" & bond_spread == .
replace bond_spread = singleA if splticrm == ""
replace bond_spread = bond_spread*100


*** merge with Dealscan
merge 1:m gvkey using "$path/0 Data/_Processed/Master_beforecompu_exposures_with_gvkeys"
drop if _merge == 2
drop _merge

keep if inrange(exposuredate_y_q, tq(2019q4),tq(2019q4))

*several borrowercompanyids get matched to same gvkey
duplicates drop gvkey gvkey_lender credit_line, force

*** calculate average spreads for TERM loans
save "$path/0 Data/_Processed/repayment_risk_aux_file", replace
keep if credit_line == 0

gen feestructure_ij = (average_spread - bond_spread)*exposure_outstanding
bysort gvkey_lender: egen feestructure_j_term = sum(feestructure_ij)

gen drawdown_ij = usage*exposure_outstanding
bysort gvkey_lender: egen drawdown_j_term = sum(drawdown_ij)
bysort gvkey_lender: egen exposure_j_term = sum(exposure_outstanding)

gen average_fees_j_term = feestructure_j_term/exposure_j_term

duplicates drop gvkey_lender, force
keep gvkey_lender drawdown_j_term feestructure_j_term average_fees_j_term exposure_j_term
rename gvkey_lender gvkey

save "$path/0 Data/_Processed/bank_level_repayment_risk_term", replace


*** calculate average spreads and repayment risk for REVOLVING loans
use "$path/0 Data/_Processed/repayment_risk_aux_file", clear
keep if credit_line == 1


*calculate variables using Sophie's approach


gen feestructure_ij = (average_spread - bond_spread)*(exposure_2019q4*share_lender)
***** make sure fee structure only captures drawn credit lines
replace feestructure_ij = 0 if drawn_balance == 0
*****
bysort gvkey_lender: egen feestructure_j = sum(feestructure_ij)
* by rating category
bysort gvkey_lender: egen feestructure_j_3B_aux = sum(feestructure_ij) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen feestructure_j_3B = mean(feestructure_j_3B_aux)
bysort gvkey_lender: egen feestructure_j_nonIG_aux = sum(feestructure_ij) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen feestructure_j_nonIG = mean(feestructure_j_nonIG_aux)
bysort gvkey_lender: egen feestructure_j_NR_aux = sum(feestructure_ij) if splticrm == ""
bysort gvkey_lender: egen feestructure_j_NR = mean(feestructure_j_NR_aux)
bysort gvkey_lender: egen feestructure_j_allAs_aux = sum(feestructure_ij) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen feestructure_j_allAs = mean(feestructure_j_allAs_aux)
drop feestructure_j_3B_aux feestructure_j_nonIG_aux feestructure_j_NR_aux feestructure_j_allAs_aux


*split repayers and non-repayers
gen feestructure_ij_repayers = feestructure_ij
gen feestructure_ij_nonrepayers = feestructure_ij
replace feestructure_ij_repayers = 0 if (average_spread - bond_spread) < 0
replace feestructure_ij_nonrepayers = 0 if (average_spread - bond_spread) > 0
bysort gvkey_lender: egen feestructure_j_repayers = sum(feestructure_ij_repayers)
bysort gvkey_lender: egen feestructure_j_nonrepayers = sum(feestructure_ij_nonrepayers)
*by rating category
bysort gvkey_lender: egen fees_j_repayers_3B_aux = sum(feestructure_ij_repayers) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen feestructure_j_repayers_3B = mean(fees_j_repayers_3B_aux)
bysort gvkey_lender: egen fees_j_repayers_nonIG_aux = sum(feestructure_ij_repayers) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen feestructure_j_repayers_nonIG = mean(fees_j_repayers_nonIG_aux)
bysort gvkey_lender: egen fees_j_repayers_NR_aux = sum(feestructure_ij_repayers) if splticrm == ""
bysort gvkey_lender: egen feestructure_j_repayers_NR = mean(fees_j_repayers_NR_aux)
bysort gvkey_lender: egen fees_j_repayers_allAs_aux = sum(feestructure_ij_repayers) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen feestructure_j_repayers_allAs = mean(fees_j_repayers_allAs_aux)
drop fees_j_repayers_3B_aux fees_j_repayers_nonIG_aux fees_j_repayers_NR_aux fees_j_repayers_allAs_aux

bysort gvkey_lender: egen fees_j_nonrepayers_3B_aux = sum(feestructure_ij_nonrepayers) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen feestructure_j_nonrepayers_3B = mean(fees_j_nonrepayers_3B_aux)
bysort gvkey_lender: egen fees_j_nonrepayers_nonIG_aux = sum(feestructure_ij_nonrepayers) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen feestructure_j_nonrepayers_nonIG = mean(fees_j_nonrepayers_nonIG_aux)
bysort gvkey_lender: egen fees_j_nonrepayers_NR_aux = sum(feestructure_ij_nonrepayers) if splticrm == ""
bysort gvkey_lender: egen feestructure_j_nonrepayers_NR = mean(fees_j_nonrepayers_NR_aux)
bysort gvkey_lender: egen fees_j_nonrepayers_allAs_aux = sum(feestructure_ij_nonrepayers) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen feestructure_j_nonrepayers_allAs = mean(fees_j_nonrepayers_allAs_aux)
drop fees_j_nonrepayers_3B_aux fees_j_nonrepayers_nonIG_aux fees_j_nonrepayers_NR_aux fees_j_nonrepayers_allAs_aux


gen fees_earned_ij = (average_spread - bond_spread)*(drawn_balance*share_lender)
bysort gvkey_lender: egen fees_earned_j = sum(fees_earned_ij)
* by rating category
bysort gvkey_lender: egen fees_earned_j_3B_aux = sum(fees_earned_ij) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen fees_earned_j_3B = mean(fees_earned_j_3B_aux)
bysort gvkey_lender: egen fees_earned_j_nonIG_aux = sum(fees_earned_ij) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen fees_earned_j_nonIG = mean(fees_earned_j_nonIG_aux)
bysort gvkey_lender: egen fees_earned_j_NR_aux = sum(fees_earned_ij) if splticrm == ""
bysort gvkey_lender: egen fees_earned_j_NR = mean(fees_earned_j_NR_aux)
bysort gvkey_lender: egen fees_earned_j_allAs_aux = sum(fees_earned_ij) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen fees_earned_j_allAs = mean(fees_earned_j_allAs_aux)
drop fees_earned_j_3B_aux fees_earned_j_nonIG_aux fees_earned_j_NR_aux fees_earned_j_allAs_aux


*split repayers and non-repayers
gen fees_earned_ij_repayers = fees_earned_ij
gen fees_earned_ij_nonrepayers = fees_earned_ij
replace fees_earned_ij_repayers = 0 if (average_spread - bond_spread) < 0
replace fees_earned_ij_nonrepayers = 0 if (average_spread - bond_spread) > 0
bysort gvkey_lender: egen fees_earned_j_repayers = sum(fees_earned_ij_repayers)
bysort gvkey_lender: egen fees_earned_j_nonrepayers = sum(fees_earned_ij_nonrepayers)
*by rating category
bysort gvkey_lender: egen fees_j_repayers_3B_aux = sum(fees_earned_ij_repayers) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen fees_earned_j_repayers_3B = mean(fees_j_repayers_3B_aux)
bysort gvkey_lender: egen fees_j_repayers_nonIG_aux = sum(fees_earned_ij_repayers) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen fees_earned_j_repayers_nonIG = mean(fees_j_repayers_nonIG_aux)
bysort gvkey_lender: egen fees_j_repayers_NR_aux = sum(fees_earned_ij_repayers) if splticrm == ""
bysort gvkey_lender: egen fees_earned_j_repayers_NR = mean(fees_j_repayers_NR_aux)
bysort gvkey_lender: egen fees_j_repayers_allAs_aux = sum(fees_earned_ij_repayers) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen fees_earned_j_repayers_allAs = mean(fees_j_repayers_allAs_aux)
drop fees_j_repayers_3B_aux fees_j_repayers_nonIG_aux fees_j_repayers_NR_aux fees_j_repayers_allAs_aux

bysort gvkey_lender: egen fees_j_nonrepayers_3B_aux = sum(fees_earned_ij_nonrepayers) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen fees_earned_j_nonrepayers_3B = mean(fees_j_nonrepayers_3B_aux)
bysort gvkey_lender: egen fees_j_nonrepayers_nonIG_aux = sum(fees_earned_ij_nonrepayers) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen fees_earned_j_nonrepayers_nonIG = mean(fees_j_nonrepayers_nonIG_aux)
bysort gvkey_lender: egen fees_j_nonrepayers_NR_aux = sum(fees_earned_ij_nonrepayers) if splticrm == ""
bysort gvkey_lender: egen fees_earned_j_nonrepayers_NR = mean(fees_j_nonrepayers_NR_aux)
bysort gvkey_lender: egen fees_j_nonrepayers_allAs_aux = sum(fees_earned_ij_nonrepayers) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen fees_earned_j_nonrepayers_allAs = mean(fees_j_nonrepayers_allAs_aux)
drop fees_j_nonrepayers_3B_aux fees_j_nonrepayers_nonIG_aux fees_j_nonrepayers_NR_aux fees_j_nonrepayers_allAs_aux



gen fees_earned_repay_ij = (average_spread - bond_spread)*usage_change_q2*exposure_2019q4*share_lender
bysort gvkey_lender: egen fees_earned_repay_j = sum(fees_earned_repay_ij)

*split repayers and non-repayers
gen fees_earned_repay_ij_repayers = fees_earned_repay_ij
gen fees_earned_repay_ij_nonrepayers = fees_earned_repay_ij
*replace fees_earned_repay_ij_repayers = 0 if usage_change_q2 < 0
*replace fees_earned_repay_ij_nonrepayers = 0 if usage_change_q2 > 0
replace fees_earned_repay_ij_repayers = 0 if (average_spread - bond_spread) < 0
replace fees_earned_repay_ij_nonrepayers = 0 if (average_spread - bond_spread) >= 0
bysort gvkey_lender: egen fees_earned_repay_j_repayers = sum(fees_earned_repay_ij_repayers)
bysort gvkey_lender: egen fees_earned_repay_j_nonrepayers = sum(fees_earned_repay_ij_nonrepayers)


gen drawdown_ij = drawn_balance*share_lender
bysort gvkey_lender: egen drawdown_j = sum(drawdown_ij)
* by rating category
bysort gvkey_lender: egen drawdown_j_3B_aux = sum(drawdown_ij) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen drawdown_j_3B = mean(drawdown_j_3B_aux)
bysort gvkey_lender: egen drawdown_j_nonIG_aux = sum(drawdown_ij) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen drawdown_j_nonIG = mean(drawdown_j_nonIG_aux)
bysort gvkey_lender: egen drawdown_j_NR_aux = sum(drawdown_ij) if splticrm == ""
bysort gvkey_lender: egen drawdown_j_NR = mean(drawdown_j_NR_aux)
bysort gvkey_lender: egen drawdown_j_allAs_aux = sum(drawdown_ij) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen drawdown_j_allAs = mean(drawdown_j_allAs_aux)
drop drawdown_j_3B_aux drawdown_j_nonIG_aux drawdown_j_NR_aux drawdown_j_allAs_aux


gen total_balance_lender = exposure_2019q4*share_lender
bysort gvkey_lender: egen exposure_j = sum(total_balance_lender)
* by rating category
bysort gvkey_lender: egen exposure_j_3B_aux = sum(total_balance_lender) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen exposure_j_3B = mean(exposure_j_3B_aux)
bysort gvkey_lender: egen exposure_j_nonIG_aux = sum(total_balance_lender) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen exposure_j_nonIG = mean(exposure_j_nonIG_aux)
bysort gvkey_lender: egen exposure_j_NR_aux = sum(total_balance_lender) if splticrm == ""
bysort gvkey_lender: egen exposure_j_NR = mean(exposure_j_NR_aux)
bysort gvkey_lender: egen exposure_j_allAs_aux = sum(total_balance_lender) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen exposure_j_allAs = mean(exposure_j_allAs_aux)
drop exposure_j_3B_aux exposure_j_nonIG_aux exposure_j_NR_aux exposure_j_allAs_aux

*other scaling factors
gen total_balance_lender_drawdown = total_balance_lender
replace total_balance_lender_drawdown = 0 if drawn_balance == 0
bysort gvkey_lender: egen exposure_drawdown_j = sum(total_balance_lender_drawdown)
gen total_balance_lender_repayment = total_balance_lender
replace total_balance_lender_repayment = 0 if usage_change_q2 == 0
bysort gvkey_lender: egen exposure_repayment_j = sum(total_balance_lender_repayment)


gen average_fees_j = feestructure_j/exposure_drawdown_j
* by rating category
gen average_fees_j_3B_aux = feestructure_j_3B/exposure_j_3B if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen average_fees_j_3B = mean(average_fees_j_3B_aux)
gen average_fees_j_nonIG_aux = feestructure_j_nonIG/exposure_j_nonIG if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen average_fees_j_nonIG = mean(average_fees_j_nonIG_aux)
gen average_fees_j_NR_aux = feestructure_j_NR/exposure_j_NR if splticrm == ""
bysort gvkey_lender: egen average_fees_j_NR = mean(average_fees_j_NR_aux)
gen average_fees_j_allAs_aux = feestructure_j_allAs/exposure_j_allAs if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen average_fees_j_allAs = mean(average_fees_j_allAs_aux)
drop average_fees_j_3B_aux average_fees_j_nonIG_aux average_fees_j_NR_aux average_fees_j_allAs_aux

*split repayers and non-repayers
gen average_fees_j_repayers = feestructure_j_repayers/exposure_drawdown_j
gen average_fees_j_nonrepayers = feestructure_j_nonrepayers/exposure_drawdown_j
* by rating category
gen average_fees_j_rep_3B_aux = feestructure_j_repayers_3B/exposure_j_3B if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen average_fees_j_repayers_3B = mean(average_fees_j_rep_3B_aux)
gen average_fees_j_rep_nonIG_aux = feestructure_j_repayers_nonIG/exposure_j_nonIG if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen average_fees_j_repayers_nonIG = mean(average_fees_j_rep_nonIG_aux)
gen average_fees_j_rep_NR_aux = feestructure_j_repayers_NR/exposure_j_NR if splticrm == ""
bysort gvkey_lender: egen average_fees_j_repayers_NR = mean(average_fees_j_rep_NR_aux)
gen average_fees_j_rep_allAs_aux = feestructure_j_repayers_allAs/exposure_j_allAs if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen average_fees_j_repayers_allAs = mean(average_fees_j_rep_allAs_aux)
drop average_fees_j_rep_3B_aux average_fees_j_rep_nonIG_aux average_fees_j_rep_NR_aux average_fees_j_rep_allAs_aux

gen average_fees_j_nonrep_3B_aux = feestructure_j_nonrepayers_3B/exposure_j_3B if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen average_fees_j_nonrepayers_3B = mean(average_fees_j_nonrep_3B_aux)
gen average_fees_j_nonrep_nonIG_aux = feestructure_j_nonrepayers_nonIG/exposure_j_nonIG if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen average_fees_j_nonrepayers_nonIG = mean(average_fees_j_nonrep_nonIG_aux)
gen average_fees_j_nonrep_NR_aux = feestructure_j_nonrepayers_NR/exposure_j_NR if splticrm == ""
bysort gvkey_lender: egen average_fees_j_nonrepayers_NR = mean(average_fees_j_nonrep_NR_aux)
gen average_fees_j_nonrep_allAs_aux = feestructure_j_nonrepayers_allAs/exposure_j_allAs if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen average_fees_j_nonrepayers_allAs = mean(average_fees_j_nonrep_allAs_aux)
drop average_fees_j_nonrep_3B_aux average_fees_j_nonrep_nonIG_aux average_fees_j_nonrep_NR_aux average_fees_j_nonrep_allAs_aux



gen repayment_ij = usage_change_q2*exposure_2019q4*share_lender
bysort gvkey_lender: egen repayment_j = sum(repayment_ij)
* by rating category
bysort gvkey_lender: egen repayment_j_3B_aux = sum(repayment_ij) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen repayment_j_3B = mean(repayment_j_3B_aux)
bysort gvkey_lender: egen repayment_j_nonIG_aux = sum(repayment_ij) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen repayment_j_nonIG = mean(repayment_j_nonIG_aux)
bysort gvkey_lender: egen repayment_j_NR_aux = sum(repayment_ij) if splticrm == ""
bysort gvkey_lender: egen repayment_j_NR = mean(repayment_j_NR_aux)
bysort gvkey_lender: egen repayment_j_allAs_aux = sum(repayment_ij) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen repayment_j_allAs = mean(repayment_j_allAs_aux)
drop repayment_j_3B_aux repayment_j_nonIG_aux repayment_j_NR_aux repayment_j_allAs_aux

*split repayers and non-repayers
gen repayment_ij_repayers = repayment_ij
gen repayment_ij_nonrepayers = repayment_ij
replace repayment_ij_repayers = 0 if repayment_ij < 0
replace repayment_ij_nonrepayers = 0 if repayment_ij > 0
bysort gvkey_lender: egen repayment_j_repayers = sum(repayment_ij_repayers)
bysort gvkey_lender: egen repayment_j_nonrepayers = sum(repayment_ij_nonrepayers)
* by rating category
bysort gvkey_lender: egen repayment_j_repayers_3B_aux = sum(repayment_ij_repayers) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen repayment_j_repayers_3B = mean(repayment_j_repayers_3B_aux)
bysort gvkey_lender: egen repayment_j_repayers_nonIG_aux = sum(repayment_ij_repayers) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen repayment_j_repayers_nonIG = mean(repayment_j_repayers_nonIG_aux)
bysort gvkey_lender: egen repayment_j_repayers_NR_aux = sum(repayment_ij_repayers) if splticrm == ""
bysort gvkey_lender: egen repayment_j_repayers_NR = mean(repayment_j_repayers_NR_aux)
bysort gvkey_lender: egen repayment_j_repayers_allAs_aux = sum(repayment_ij_repayers) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen repayment_j_repayers_allAs = mean(repayment_j_repayers_allAs_aux)
drop repayment_j_repayers_3B_aux repayment_j_repayers_nonIG_aux repayment_j_repayers_NR_aux repayment_j_repayers_allAs_aux

bysort gvkey_lender: egen repayment_j_nonrep_3B_aux = sum(repayment_ij_nonrepayers) if (splticrm == "BBB+" | splticrm == "BBB" | splticrm == "BBB-")
bysort gvkey_lender: egen repayment_j_nonrepayers_3B = mean(repayment_j_nonrep_3B_aux)
bysort gvkey_lender: egen repayment_j_nonrep_nonIG_aux = sum(repayment_ij_nonrepayers) if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")
bysort gvkey_lender: egen repayment_j_nonrepayers_nonIG = mean(repayment_j_nonrep_nonIG_aux)
bysort gvkey_lender: egen repayment_j_nonrep_NR_aux = sum(repayment_ij_nonrepayers) if splticrm == ""
bysort gvkey_lender: egen repayment_j_nonrepayers_NR = mean(repayment_j_nonrep_NR_aux)
bysort gvkey_lender: egen repayment_j_nonrep_allAs_aux = sum(repayment_ij_nonrepayers) if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-" | splticrm == "A+" | splticrm == "A" | splticrm == "A-")
bysort gvkey_lender: egen repayment_j_nonrepayers_allAs = mean(repayment_j_nonrep_allAs_aux)
drop repayment_j_nonrep_3B_aux repayment_j_nonrep_nonIG_aux repayment_j_nonrep_NR_aux repayment_j_nonrep_allAs_aux


*risk weighted repayments
replace repayment_ij_repayers = 0.2*repayment_ij_repayers if (splticrm == "AAA" | splticrm == "AA+" | splticrm == "AA" | splticrm == "AA-")
replace repayment_ij_repayers = 0.5*repayment_ij_repayers if (splticrm == "A+" | splticrm == "A" | splticrm == "A-")
replace repayment_ij_repayers = 1.5*repayment_ij_repayers if (splticrm == "B+" |splticrm == "B" |splticrm == "B-" |splticrm == "BB+" |splticrm == "BB" |splticrm == "BB-" | splticrm == "CCC+" | splticrm == "CCC" | splticrm == "CCC-" | splticrm == "CC" | splticrm == "D" | splticrm == "SD")

bysort gvkey_lender: egen repayment_j_repayers_rw = sum(repayment_ij_repayers)



duplicates drop gvkey_lender, force
keep gvkey_lender drawdown_j feestructure_j average_fees_j exposure_j exposure_drawdown_j exposure_repayment_j repayment_j fees_earned_j fees_earned_repay_j ///
feestructure_j_repayers feestructure_j_nonrepayers  average_fees_j_repayers average_fees_j_nonrepayers repayment_j_repayers repayment_j_nonrepayers fees_earned_j_repayers fees_earned_j_nonrepayers fees_earned_repay_j_repayers fees_earned_repay_j_nonrepayers repayment_j_repayers_rw ///
drawdown_j_3B feestructure_j_3B feestructure_j_repayers_3B feestructure_j_nonrepayers_3B average_fees_j_3B average_fees_j_repayers_3B average_fees_j_nonrepayers_3B exposure_j_3B repayment_j_3B repayment_j_repayers_3B repayment_j_nonrepayers_3B fees_earned_j_nonrepayers_3B fees_earned_j_repayers_3B ///
 drawdown_j_nonIG feestructure_j_nonIG feestructure_j_repayers_nonIG feestructure_j_nonrepayers_nonIG average_fees_j_nonIG average_fees_j_repayers_nonIG average_fees_j_nonrepayers_nonIG exposure_j_nonIG repayment_j_nonIG repayment_j_repayers_nonIG repayment_j_nonrepayers_nonIG fees_earned_j_nonrepayers_nonIG fees_earned_j_repayers_nonIG ///
 drawdown_j_NR feestructure_j_NR feestructure_j_repayers_NR feestructure_j_nonrepayers_NR average_fees_j_NR average_fees_j_repayers_NR average_fees_j_nonrepayers_NR exposure_j_NR repayment_j_NR repayment_j_repayers_NR repayment_j_nonrepayers_NR fees_earned_j_nonrepayers_NR fees_earned_j_repayers_NR ///
 drawdown_j_allAs feestructure_j_allAs feestructure_j_repayers_allAs feestructure_j_nonrepayers_allAs average_fees_j_allAs average_fees_j_repayers_allAs average_fees_j_nonrepayers_allAs exposure_j_allAs repayment_j_allAs repayment_j_repayers_allAs repayment_j_nonrepayers_allAs fees_earned_j_nonrepayers_allAs fees_earned_j_repayers_allAs

foreach var of varlist feestructure_j average_fees_j exposure_j exposure_drawdown_j exposure_repayment_j fees_earned_j fees_earned_repay_j ///
feestructure_j_repayers feestructure_j_nonrepayers  average_fees_j_repayers average_fees_j_nonrepayers fees_earned_j_repayers fees_earned_j_nonrepayers fees_earned_repay_j_repayers fees_earned_repay_j_nonrepayers repayment_j_repayers_rw ///
 feestructure_j_3B feestructure_j_repayers_3B feestructure_j_nonrepayers_3B average_fees_j_3B average_fees_j_repayers_3B average_fees_j_nonrepayers_3B exposure_j_3B fees_earned_j_nonrepayers_3B fees_earned_j_repayers_3B ///
 feestructure_j_nonIG feestructure_j_repayers_nonIG feestructure_j_nonrepayers_nonIG average_fees_j_nonIG average_fees_j_repayers_nonIG average_fees_j_nonrepayers_nonIG exposure_j_nonIG  fees_earned_j_nonrepayers_nonIG fees_earned_j_repayers_nonIG ///
 feestructure_j_NR feestructure_j_repayers_NR feestructure_j_nonrepayers_NR average_fees_j_NR average_fees_j_repayers_NR average_fees_j_nonrepayers_NR exposure_j_NR fees_earned_j_nonrepayers_NR fees_earned_j_repayers_NR ///
 feestructure_j_allAs feestructure_j_repayers_allAs feestructure_j_nonrepayers_allAs average_fees_j_allAs average_fees_j_repayers_allAs average_fees_j_nonrepayers_allAs exposure_j_allAs fees_earned_j_nonrepayers_allAs fees_earned_j_repayers_allAs{
	sum `var', detail
	replace `var' = `r(p50)' if `var' == .
}


foreach var of varlist drawdown_j repayment_j ///
repayment_j_repayers repayment_j_nonrepayers repayment_j_repayers_rw ///
drawdown_j_3B repayment_j_3B repayment_j_repayers_3B repayment_j_nonrepayers_3B  ///
 drawdown_j_nonIG repayment_j_nonIG repayment_j_repayers_nonIG repayment_j_nonrepayers_nonIG  ///
 drawdown_j_NR repayment_j_NR repayment_j_repayers_NR repayment_j_nonrepayers_NR  ///
 drawdown_j_allAs repayment_j_allAs repayment_j_repayers_allAs repayment_j_nonrepayers_allAs{
	replace `var' = 0 if `var' == .
}


rename gvkey_lender gvkey


merge 1:1 gvkey using "$path/0 Data/_Processed/bank_level_repayment_risk_term", nogenerate
save "$path/0 Data/_Processed/bank_level_repayment_risk", replace

